// 包：com.jdone.compus.service
package com.jdone.compus.service;

import com.jdone.compus.dto.*;
import com.jdone.compus.enums.OrderStatus;
import com.jdone.compus.model.BlowDevice;
import com.jdone.compus.model.BlowUsageRecord;
import com.jdone.compus.repository.BlowDeviceRepository;
import com.jdone.compus.repository.BlowUsageRecordRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.EntityNotFoundException;
import javax.persistence.criteria.Predicate;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;

import java.util.ArrayList;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class BlowUsageRecordService {

    @Autowired
    private BlowUsageRecordRepository repository;

    @Autowired
    private BlowDeviceRepository deviceRepository;


    /**
     * 根据业务订单号查询
     */
    public Optional<BlowUsageRecord> findByBusinessOrderNo(String businessOrderNo) {
        return repository.findByBusinessOrderNo(businessOrderNo);
    }

    /**
     * 根据业务订单号更新订单状态并返回最新对象
     */
    public BlowUsageRecord updateOrderStatus(String businessOrderNo, OrderStatus newStatus) {
        BlowUsageRecord record = repository.findByBusinessOrderNo(businessOrderNo).orElseThrow(() -> new EntityNotFoundException("找不到业务订单号：" + businessOrderNo));
        record.setOrderStatus(newStatus);
        return repository.save(record);
    }

    /**
     * 根据ID查询使用记录
     */
    public BlowUsageRecord getById(Long id) {
        return repository.findById(id).orElseThrow(() -> new RuntimeException("使用记录未找到"));
    }

    /**
     * 分页查询使用记录
     */
    public Page<BlowUsageRecord> getAll(Pageable pageable) {
        return repository.findAll(pageable);
    }

    /**
     * 修改使用记录
     */
    public BlowUsageRecord update(Long id, BlowUsageRecord dto) {
        BlowUsageRecord entity = getById(id);
        entity.setUserAccount(dto.getUserAccount());
        entity.setUserType(dto.getUserType());
        entity.setUserPhone(dto.getUserPhone());
        entity.setBorrowTime(dto.getBorrowTime());
        entity.setReturnTime(dto.getReturnTime());
        entity.setMinutes(dto.getMinutes());
        entity.setFee(dto.getFee());
        return repository.save(entity);
    }

    /**
     * 删除使用记录
     */
    public void delete(Long id) {
        BlowUsageRecord entity = getById(id);
        repository.delete(entity);
    }


    @Transactional(readOnly = true)
    public Page<BlowUsageRecordDto> findByCriteria(BlowUsageRecordQuery q, Pageable pageable) {
        Specification<BlowUsageRecord> spec = buildSpecification(q);
        Page<BlowUsageRecord> usagePage = repository.findAll(spec, pageable);

        List<BlowUsageRecord> usages = usagePage.getContent();
        if (usages.isEmpty()) {
            return new PageImpl<>(Collections.emptyList(), pageable, usagePage.getTotalElements());
        }

        // 收集 deviceId 并批量读取设备（避免 N+1）
        List<Long> deviceIds = usages.stream().map(BlowUsageRecord::getDeviceId).filter(Objects::nonNull).distinct().collect(Collectors.toList());

        Map<Long, BlowDevice> deviceMap = deviceIds.isEmpty() ? Collections.emptyMap() : deviceRepository.findAllById(deviceIds).stream().collect(Collectors.toMap(BlowDevice::getId, Function.identity()));

        List<BlowUsageRecordDto> dtoList = usages.stream().map(u -> {
            BlowUsageRecordDto dto = new BlowUsageRecordDto();
            // 根据你的 DTO 结构，下面两种方式二选一：
            dto.setUsageRecord(u);
            dto.setDevice(deviceMap.get(u.getDeviceId()));
            return dto;
        }).collect(Collectors.toList());

        return new PageImpl<>(dtoList, pageable, usagePage.getTotalElements());
    }


    private Specification<BlowUsageRecord> buildSpecification(BlowUsageRecordQuery q) {
        return (root, cq, cb) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (StringUtils.hasText(q.getBusinessOrderNo())) {
                predicates.add(cb.like(cb.lower(root.get("businessOrderNo")), "%" + q.getBusinessOrderNo().toLowerCase() + "%"));
            }
            if (StringUtils.hasText(q.getUsername())) {
                // 使用模糊匹配，也可改为 equals
                predicates.add(cb.like(cb.lower(root.get("username")), "%" + q.getUsername().toLowerCase() + "%"));
            }
            if (StringUtils.hasText(q.getUserAccount())) {
                predicates.add(cb.equal(root.get("userAccount"), q.getUserAccount().trim()));
            }
            if (StringUtils.hasText(q.getUserPhone())) {
                predicates.add(cb.like(cb.lower(root.get("userPhone")), "%" + q.getUserPhone().toLowerCase() + "%"));
            }

            if (q.getBorrowTimeStart() != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("borrowTime"), q.getBorrowTimeStart()));
            }
            if (q.getBorrowTimeEnd() != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("borrowTime"), q.getBorrowTimeEnd()));
            }

            // device 相关条件需要 join device
            if (StringUtils.hasText(q.getImei()) || StringUtils.hasText(q.getManufacturer())) {
                Join<BlowUsageRecord, BlowDevice> deviceJoin = root.join("device", JoinType.LEFT);
                if (StringUtils.hasText(q.getImei())) {
                    predicates.add(cb.like(cb.lower(deviceJoin.get("imei")), "%" + q.getImei().toLowerCase() + "%"));
                }
                if (StringUtils.hasText(q.getManufacturer())) {
                    predicates.add(cb.like(cb.lower(deviceJoin.get("manufacturer")), "%" + q.getManufacturer().toLowerCase() + "%"));
                }
                // 如果有 join，避免重复记录
                cq.distinct(true);
            }

            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }

    /**
     * 根据 query 和 pageable 查询 UsageRecord，并批量查询对应的设备，返回 DTO page
     */
    public Page<BlowUsageRecordDto> findByCriteriaWithDevice(BlowUsageRecordQuery query, Pageable pageable) {

        Page<BlowUsageRecord> usagePage = repository.findAll(/* buildSpecification(query) */ (root, cq, cb) -> {
            // 简单示例，实际请用你的 query->spec 构造
            return cb.conjunction();
        }, pageable);

        List<BlowUsageRecord> usages = usagePage.getContent();
        if (usages.isEmpty()) {
            return new PageImpl<>(Collections.emptyList(), pageable, usagePage.getTotalElements());
        }

        // 批量收集 deviceId，去重
        List<Long> deviceIds = usages.stream().map(BlowUsageRecord::getDeviceId).filter(Objects::nonNull).distinct().collect(Collectors.toList());

        // 批量读取设备并转为 map
        Map<Long, BlowDevice> deviceMap;
        if (!deviceIds.isEmpty()) {
            List<BlowDevice> devices = deviceRepository.findAllById(deviceIds);
            deviceMap = devices.stream().collect(Collectors.toMap(BlowDevice::getId, Function.identity()));
        } else {
            deviceMap = new HashMap<>();
        }

        // 组装 DTO 列表
        List<BlowUsageRecordDto> dtoList = usages.stream().map(u -> {
            BlowUsageRecordDto dto = new BlowUsageRecordDto();
            dto.setUsageRecord(u);
            dto.setDevice(deviceMap.get(u.getDeviceId())); // 可能为 null
            return dto;
        }).collect(Collectors.toList());

        return new PageImpl<>(dtoList, pageable, usagePage.getTotalElements());
    }

    /**
     * 全量查询（导出用）
     */
    public List<BlowUsageRecord> findByCriteria(BlowUsageRecordQuery q) {
        Specification<BlowUsageRecord> spec = buildSpecification(q);
        return repository.findAll(spec);
    }

    public BlowUsageRecord create(BlowUsageRecord dto) {

        // 1. 确保设备存在
        BlowDevice device = deviceRepository.findById(dto.getDeviceId()).orElseThrow(() -> new EntityNotFoundException("设备不存在: " + dto.getDeviceId()));
        dto.setDevice(device);

        // 2. 补齐 borrowTime 和 returnTime（先用 now 占位，后面可能覆盖）
        LocalDateTime now = LocalDateTime.now().truncatedTo(ChronoUnit.SECONDS);
        if (dto.getBorrowTime() == null) {
            dto.setBorrowTime(now);
        }
        if (dto.getReturnTime() == null) {
            dto.setReturnTime(dto.getBorrowTime().plusMinutes(dto.getMinutes()));
        }

        // 3. 查最近一条记录，做冲突或续用处理
        repository.findTopByDeviceIdOrderByReturnTimeDesc(dto.getDeviceId()).ifPresent(last -> {
            LocalDateTime lastEnd = last.getReturnTime();
            LocalDateTime newStart = dto.getBorrowTime();

            // 如果新借用时间在上次归还时间之前或相等，则需要特殊处理
            if (!newStart.isAfter(lastEnd)) {
                // 同一账号：续用
                if (last.getUserAccount().equals(dto.getUserAccount())) {
                    // 把借用时间强制设为上一笔的归还时间
                    dto.setBorrowTime(lastEnd);
                    // 然后重新计算归还时间
                    dto.setReturnTime(lastEnd.plusMinutes(dto.getMinutes()));
                } else {
                    // 不同账号：直接报错
                    throw new IllegalStateException("设备正在被【" + last.getUserAccount() + "】使用，归还时间：" + lastEnd);
                }
            }
        });

        // 4. 保存：@PrePersist 还会填充 createdAt/updatedAt/orderStatus/businessOrderNo
        return repository.save(dto);
    }


    /**
     * 按天统计已支付订单的总数、总金额及平均消费金额
     */
    public Map<String, Object> getPaidOrderStats(LocalDate start, LocalDate end) {
        // 1. 把 LocalDate 补齐到 LocalDateTime
        LocalDateTime startDt = (start == null) ? null : start.atStartOfDay();
        LocalDateTime endDt = (end == null) ? null : end.atTime(23, 59, 59);

        // 2. 投影查询
        List<OrderStatsProjection> statsList = repository.countAndSumByStatusAndTime(OrderStatus.PAID, startDt, endDt);

        // 3. 取出结果（投影列表里只会有 0 或 1 条记录）
        long totalCount = 0L;
        BigDecimal totalFee = BigDecimal.ZERO;
        if (!statsList.isEmpty()) {
            OrderStatsProjection stats = statsList.get(0);
            totalCount = stats.getTotalCount() != null ? stats.getTotalCount() : 0L;
            totalFee = stats.getTotalFee() != null ? stats.getTotalFee() : BigDecimal.ZERO;
        }

        // 4. 计算平均消费，保留两位小数
        BigDecimal averageFee = BigDecimal.ZERO;
        if (totalCount > 0) {
            averageFee = totalFee.divide(BigDecimal.valueOf(totalCount), 2, RoundingMode.HALF_UP);
        }

        // 5. 返回 Map
        Map<String, Object> result = new HashMap<>();
        result.put("totalCount", totalCount);
        result.put("totalFee", totalFee);
        result.put("averageFee", averageFee);
        return result;
    }

    public List<DailyFeeDTO> getDailyFee(LocalDate start, LocalDate end) {
        // 1. 把 LocalDate 补齐到 LocalDateTime
        LocalDateTime startDt = start == null ? null : start.atStartOfDay();
        LocalDateTime endDt = end == null ? null : end.atTime(23, 59, 59);

        // 2. 调用 Repository
        List<Object[]> rows = repository.findDailyFeeByStatusAndDateTime(OrderStatus.PAID, startDt, endDt);

        // 3. 映射成 DTO
        return rows.stream().map(row -> {
            // row[0] 是 java.sql.Date
            java.sql.Date sqlDate = (java.sql.Date) row[0];
            LocalDate day = sqlDate.toLocalDate();

            BigDecimal totalFee = (BigDecimal) row[1];
            return new DailyFeeDTO(day, totalFee);
        }).collect(Collectors.toList());
    }


    /**
     * 按照时间来统计消费金额和消费订单数
     *
     * @param startDateStr
     * @param endDateStr
     * @param granularity
     * @return
     */
    public List<DayStatusStatsDTO> getUsageStats(String startDateStr, String endDateStr, String granularity) {
        if (!"day".equalsIgnoreCase(granularity) && !"month".equalsIgnoreCase(granularity)) {
            throw new IllegalArgumentException("granularity must be 'day' or 'month'");
        }

        // 1) 如果 DB 没有数据，直接返回空列表
        LocalDateTime dbMin = repository.findMinBorrowTime();
        if (dbMin == null) {
            return Collections.emptyList();
        }
        LocalDateTime dbMax = repository.findMaxBorrowTime();

        if ("day".equalsIgnoreCase(granularity)) {
            // 解析 start/end（若为 null 则用 dbMin/dbMax）
            LocalDate start = (startDateStr == null || startDateStr.isEmpty()) ? dbMin.toLocalDate() : LocalDate.parse(startDateStr); // 格式 yyyy-MM-dd
            LocalDate end = (endDateStr == null || endDateStr.isEmpty()) ? dbMax.toLocalDate() : LocalDate.parse(endDateStr);

            if (end.isBefore(start)) throw new IllegalArgumentException("endDate must be >= startDate");

            String sqlStart = start.atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            String sqlEnd = end.plusDays(1).atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

            List<Object[]> rows = repository.findStatsByDayGroupStatus(sqlStart, sqlEnd);
//            Map<String, UsageStatsDto> map = rows.stream().collect(Collectors.toMap(r -> (String) r[0], r -> {
//                Number cnt = (Number) r[1];
//                BigDecimal sum = r[2] == null ? BigDecimal.ZERO : (BigDecimal) r[2];
//                return new UsageStatsDto((String) r[0], cnt == null ? 0L : cnt.longValue(), sum);
//            }));
//
//            List<UsageStatsDto> out = new ArrayList<>();
//            LocalDate cur = start;
//            while (!cur.isAfter(end)) {
//                String p = cur.format(DateTimeFormatter.ISO_LOCAL_DATE);
//                out.add(map.getOrDefault(p, new UsageStatsDto(p, 0L, BigDecimal.ZERO)));
//                cur = cur.plusDays(1);
//            }
//            return out;
            List<DayStatusStatsDTO> out = new ArrayList<>();

            for (Object[] r : rows) {
                String period = (r[0] == null) ? null : r[0].toString();

                // 注意 JDBC 返回类型可能是 BigDecimal/Long/Integer 等，先以 Number 处理再转换
                Long createdTotal = (r[1] == null) ? 0L : ((Number) r[1]).longValue();
                BigDecimal createdTotalAmount = (r[2] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[2].toString());

                Long usedTotal = (r[3] == null) ? 0L : ((Number) r[3]).longValue();
                BigDecimal usedTotalAmount = (r[4] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[4].toString());

                Long cancelledTotal = (r[5] == null) ? 0L : ((Number) r[5]).longValue();
                BigDecimal cancelledTotalAmount = (r[6] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[6].toString());

                DayStatusStatsDTO dto = new DayStatusStatsDTO(
                        period,
                        createdTotal, createdTotalAmount,
                        usedTotal, usedTotalAmount,
                        cancelledTotal, cancelledTotalAmount
                );
                out.add(dto);
            }
            return out;
        } else {
            // month
            YearMonth start = (startDateStr == null || startDateStr.isEmpty()) ? YearMonth.from(dbMin.toLocalDate()) : YearMonth.parse(startDateStr); // 格式 yyyy-MM
            YearMonth end = (endDateStr == null || endDateStr.isEmpty()) ? YearMonth.from(dbMax.toLocalDate()) : YearMonth.parse(endDateStr);

            if (end.isBefore(start)) throw new IllegalArgumentException("end must be >= start");

            String sqlStart = start.atDay(1).atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            String sqlEnd = end.plusMonths(1).atDay(1).atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

            List<Object[]> rows = repository.findStatsByMonthGroupStatus(sqlStart, sqlEnd);
//            Map<String, UsageStatsDto> map = rows.stream().collect(Collectors.toMap(r -> (String) r[0], r -> {
//                Number cnt = (Number) r[1];
//                BigDecimal sum = r[2] == null ? BigDecimal.ZERO : (BigDecimal) r[2];
//                return new UsageStatsDto((String) r[0], cnt == null ? 0L : cnt.longValue(), sum);
//            }));
//
//            List<UsageStatsDto> out = new ArrayList<>();
//            YearMonth cur = start;
//            while (!cur.isAfter(end)) {
//                String p = cur.toString(); // yyyy-MM
//                out.add(map.getOrDefault(p, new UsageStatsDto(p, 0L, BigDecimal.ZERO)));
//                cur = cur.plusMonths(1);
//            }
//            return out;
            List<DayStatusStatsDTO> out = new ArrayList<>();

            for (Object[] r : rows) {
                String period = (r[0] == null) ? null : r[0].toString();

                // 注意 JDBC 返回类型可能是 BigDecimal/Long/Integer 等，先以 Number 处理再转换
                Long createdTotal = (r[1] == null) ? 0L : ((Number) r[1]).longValue();
                BigDecimal createdTotalAmount = (r[2] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[2].toString());

                Long usedTotal = (r[3] == null) ? 0L : ((Number) r[3]).longValue();
                BigDecimal usedTotalAmount = (r[4] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[4].toString());

                Long cancelledTotal = (r[5] == null) ? 0L : ((Number) r[5]).longValue();
                BigDecimal cancelledTotalAmount = (r[6] == null) ? BigDecimal.ZERO :
                        new BigDecimal(r[6].toString());

                DayStatusStatsDTO dto = new DayStatusStatsDTO(
                        period,
                        createdTotal, createdTotalAmount,
                        usedTotal, usedTotalAmount,
                        cancelledTotal, cancelledTotalAmount
                );
                out.add(dto);

            }
            return out;
        }
    }
}
